package com.eascs.admin.oldO2olike.dao;

import java.util.GregorianCalendar;

import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.o2o.internal.util.BaseUtils;
import com.eascs.admin.oldO2olike.form.OldO2oLikeForm;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.web.sso.UserHolder;

/**
 * 
 * 贷款查询dao
 *
 */
@Repository
public class OldO2oLikeDao extends BaseO2oDao{
	/**
	 * 征信贷款分页查询
	 * @param OldO2oLikeForm
	 * @return
	 */
	public PageResult<Object[]> findZXByPage(OldO2oLikeForm oldO2oLikeForm,String type) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class,oldO2oLikeForm.getPageSize() ,
				oldO2oLikeForm.getPageIndex() == null ? BaseUtils.PAGE_INDEX : oldO2oLikeForm.getPageIndex());
		StringBuffer sql = new StringBuffer();
		if ("export".equals(type)) {
			sql.append(" SELECT bill_code, ");
			sql.append(" (select d.item_name from PUB_DDITEM d where d.DD_NO=10340 and d.item_code= zx_bank) as zx_bank,");
			sql.append(" user_full_name,operator,ltdName,");
			sql.append(" (select d.item_name from PUB_DDITEM d where d.DD_NO=11321 and d.item_code= loanType) as loanType,");
			sql.append(" (select d.item_name from pub_dditem_o2o d where d.dd_no='2' and d.item_code= wf_type) as wf_type,");
			sql.append(" loana_mount,date_format(cdate, '%Y-%m-%d %H:%i:%s') as 'cdate',province_name ");
			sql.append(" from (");
		}else {
			sql.append(" SELECT * from (");
		}
		sql.append(" select l.loanapply_no,l.bill_code,  zx.zx_bank ,");// 征信银行取o2o_zx表中的值zx.zx_bank
		sql.append(" u.user_full_name ,l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name,");
		sql.append("  l.loan_type as 'loanType',l.wf_type,");
//		sql.append(" (case when  l.wf_type ='NEW' then 'NEW' when l.wf_type ='Z' then 'RUN' else 'CLO' end) as 'wfType',");
		sql.append(" (case when  s.wf_status is NULL then 'NEW'  else s.wf_status end) as 'wfType',");
		sql.append(" round(l.loana_mount/10000 ,2) as 'loana_mount',  ");
		sql.append(" l.create_date as 'cdate',s.start_date as 'sdate',s.end_date as 'enddate',opr.province_code,l.LTD,l.creat_ro,operator_no");
		sql.append(" from o2o_loanapply l ");
		sql.append(" LEFT JOIN o2o_loan_user u on l.loan_id=u.loan_id and u.spouse_id is null and u.sponsor_type is null  ");
		sql.append(" LEFT JOIN o2o_zx zx on u.zx_no = zx.id   ");// 征信银行取o2o_zx表中的值zx.zx_bank
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd");
		sql.append(" LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ");
		sql.append(" LEFT JOIN wf_type_status s on s.loanapply_no=l.loanapply_no and s.wf_type in ('ZX')");
		sql.append(" where  l.islock='N' and l.loanapply_no=u.loanapply_no  ");
		sql.append(" and l.wf_type !='T'  and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL ");
		sql.append(" union");
		sql.append(" SELECT l.loanapply_no,l.bill_code,  zx.zx_bank ,");
		sql.append(" u.user_full_name ,l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name,");
		sql.append("  l.loan_type as 'loanType',l.wf_type,");
//		sql.append(" (case when  l.wf_type ='NEW' then 'NEW' when l.wf_type ='Z' then 'RUN' else 'CLO' end) as 'wfType',");
		sql.append(" s.wf_status as 'wfType',");
		sql.append(" round(l.loana_mount/10000 ,2) as 'loana_mount', ");
		sql.append(" l.create_date as 'cdate',s.start_date as 'sdate',NULL as 'enddate',opr.province_code,l.LTD,l.creat_ro,operator_no");
		sql.append(" from o2o_loanapply l");
		sql.append(" LEFT JOIN o2o_loan_user u on l.loan_id=u.loan_id and u.spouse_id is null and u.sponsor_type is null  ");
		sql.append(" LEFT JOIN o2o_zx zx on u.zx_no = zx.id   ");// 征信银行取o2o_zx表中的值zx.zx_bank
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append("  LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ,");
		sql.append(" (SELECT * from (");
		sql.append(" SELECT s.* from wf_type_status s where ");
		sql.append(" s.wf_type in ('ZX','NS','BRT') group by loanapply_no,wf_type order by s.start_date	desc");
		sql.append(" ) t group by t.loanapply_no having count(*) =1 ");
		sql.append(" ) s ");
		sql.append(" where  l.wf_type ='T' and s.loanapply_no=l.loanapply_no and s.wf_status in ('NEW','RUN') ");
		sql.append(" and l.loanapply_no=u.loanapply_no and u.spouse_id is null and u.sponsor_type is null");
		sql.append("  and  l.bill_code is not NULL ");
		sql.append(" union");
		sql.append(" SELECT l.loanapply_no,l.bill_code,  zx.zx_bank ,");
		sql.append(" u.user_full_name ,l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name,");
		sql.append("  l.loan_type as 'loanType',l.wf_type,");
//		sql.append(" (case when  l.wf_type ='NEW' then 'NEW' when l.wf_type ='Z' then 'RUN' else 'CLO' end) as 'wfType',");
		sql.append(" 'NEW' as 'wfType',");
		sql.append(" round(l.loana_mount/10000 ,2) as 'loana_mount', ");
		sql.append(" l.create_date as 'cdate',NULL as 'sdate',NULL as 'enddate',opr.province_code,l.LTD,l.creat_ro,operator_no");
		sql.append("  from o2o_loanapply l");
		sql.append(" LEFT JOIN o2o_loan_user u on l.loan_id=u.loan_id and u.spouse_id is null and u.sponsor_type is null  ");
		sql.append(" LEFT JOIN o2o_zx zx on u.zx_no = zx.id   ");// 征信银行取o2o_zx表中的值zx.zx_bank
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append("  LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no  ");
		sql.append(" where    l.wf_type ='T' and  l.bill_code is not NULL ");
		sql.append(" and l.loanapply_no=u.loanapply_no and u.spouse_id is null and u.sponsor_type is null");
		sql.append("  and  l.loanapply_no not in(SELECT s.loanapply_no from  wf_type_status s )");
		sql.append(" ) t where 1=1 and wf_type not in ('YQ','DC','HN','O') ");
		
		sql.append(" and (creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
			sql.append(" or operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(LTD)**/  1=1 ) ");
		}
		if (StringUtil.hasText(oldO2oLikeForm.getBillCode())) {
			sql.append(" and bill_code like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getBillCode());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getLoanType())) {
			sql.append(" and loanType in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(oldO2oLikeForm.getLoanType().length()>4){
				loanTypeA = oldO2oLikeForm.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+oldO2oLikeForm.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			sql.append(loanType);
			sql.append(")");
		}
		if (StringUtil.hasText(oldO2oLikeForm.getPayMentStatus())) {
				if ("O".equals(oldO2oLikeForm.getPayMentStatus())) {
					sql.append(" and wf_type in ('YQ','DC','HN','O')");
				} else if("ZXSX".equals(oldO2oLikeForm.getPayMentStatus())){
					sql.append(" and wf_type=0");
				}else {
					sql.append(" and wf_type in (");
					String wfTypeA[] = {};
					String wfType = "";
					StringBuffer wfTypes = new StringBuffer();
					if (oldO2oLikeForm.getPayMentStatus().contains(",")) {
						wfTypeA = oldO2oLikeForm.getPayMentStatus().split(",");
						for (int i = 0; i < wfTypeA.length; i++) {
							wfTypes.append("'" + wfTypeA[i] + "',");
						}
					} else {
						wfTypes.append("'" + oldO2oLikeForm.getPayMentStatus() + "',");
					}
					wfType = wfTypes.toString().substring(0, wfTypes.toString().length() - 1);
					sql.append(wfType);
					sql.append(" ) ");
				}
		}
		
		if (StringUtil.hasText(oldO2oLikeForm.getOperatorName())) {
			sql.append(" and operator like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getOperatorName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getUserSurname())) {
			sql.append(" and user_full_name like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getUserSurname());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getLoadName())) {
			sql.append(" and ltdName like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getLoadName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getStartTime())) {
			sql.append(" and cdate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getStartTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEndTime())) {
			sql.append(" and cdate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEndTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		if (StringUtil.hasText(oldO2oLikeForm.getStTime())) {
			sql.append(" and sdate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getStTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getSendTime())) {
			sql.append(" and sdate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getSendTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEendTime())) {
			sql.append(" and enddate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getEendTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEendendTime())) {
			sql.append(" and enddate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEendendTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		if (!StringUtil.isEmpty(oldO2oLikeForm.getHostProvince())) {
			sql.append(" AND  province_code  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getHostProvince());
		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getBillStatus())) {
			sql.append(" AND  wfType  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getBillStatus());
		}
		sql.append(" ORDER BY cdate desc ");
		pageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(pageQuery);
	}
	
//	public PageResult<Object[]> findZXByPageExport(OldO2oLikeForm oldO2oLikeForm) {
//		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class,oldO2oLikeForm.getPageSize() ,
//				oldO2oLikeForm.getPageIndex() == null ? BaseUtils.PAGE_INDEX : oldO2oLikeForm.getPageIndex());
//		StringBuffer sql = new StringBuffer();
//		sql.append(" SELECT * from (");
//		sql.append(" select l.bill_code,  l.bank_type ,");
//		sql.append(" u.user_full_name ,l.operator_name as 'operator', ");
//		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',");
//		sql.append("  l.loan_type as 'loanType',l.wf_type,");
////		sql.append(" (case when  l.wf_type ='NEW' then 'NEW' when l.wf_type ='Z' then 'RUN' else 'CLO' end) as 'wfType',");
//		sql.append(" s.wf_status as 'wfType',");
//		sql.append(" round(l.loana_mount ,4)/10000 as 'loana_mount',  ");
//		sql.append(" date_format(l.create_date, '%Y-%m-%d %H:%i:%s') as 'cdate',opr.province_name");//,s.start_date as 'sdate',s.end_date as 'enddate'
//		sql.append(" from o2o_loanapply l ");
//		sql.append(" LEFT JOIN o2o_loan_user u on l.loan_id=u.loan_id and u.spouse_id is null and u.sponsor_type is null  ");
//		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd");
//		sql.append(" LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ");
//		sql.append(" LEFT JOIN wf_type_status s on s.loanapply_no=l.loanapply_no and s.wf_type in ('ZX')");
//		sql.append(" where  l.islock='N' and l.loanapply_no=u.loanapply_no  ");
//		
//		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
//		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
//			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
//		}else {
//			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
//		}
//		
//		sql.append(" and l.wf_type !='T'  and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL ");
//		sql.append(" union");
//		sql.append(" SELECT l.bill_code,  l.bank_type ,");
//		sql.append(" u.user_full_name ,l.operator_name as 'operator', ");
//		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',");
//		sql.append("  l.loan_type as 'loanType',l.wf_type,");
////		sql.append(" (case when  l.wf_type ='NEW' then 'NEW' when l.wf_type ='Z' then 'RUN' else 'CLO' end) as 'wfType',");
//		sql.append(" s.wf_status as 'wfType',");
//		sql.append(" round(l.loana_mount ,4)/10000  as 'loana_mount',  ");
//		sql.append(" date_format(l.create_date, '%Y-%m-%d %H:%i:%s') as 'cdate',opr.province_name");//s.start_date as 'sdate',NULL as 'enddate',
//		sql.append(" from o2o_loanapply l");
//		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
//		sql.append("  LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ,");
//		sql.append(" (SELECT * from (");
//		sql.append(" SELECT s.* from wf_type_status s where ");
//		sql.append(" s.wf_type in ('ZX','NS','BRT') group by loanapply_no,wf_type order by s.start_date	desc");
//		sql.append(" ) t group by t.loanapply_no having count(*) =1 ");
//		sql.append(" ) s ,o2o_loan_user u ");
//		sql.append(" where   l.wf_type ='T' and s.loanapply_no=l.loanapply_no and s.wf_status in ('NEW','RUN') ");
//		
//		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
//		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
//			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
//		}else {
//			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
//		}
//		
//		sql.append(" and l.loanapply_no=u.loanapply_no and u.spouse_id is null and u.sponsor_type is null");
//		sql.append("  and  l.bill_code is not NULL ");
//		sql.append(" union");
//		sql.append(" SELECT l.bill_code,  l.bank_type ,");
//		sql.append(" u.user_full_name ,l.operator_name as 'operator', ");
//		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',");
//		sql.append("  l.loan_type as 'loanType',l.wf_type,");
////		sql.append(" (case when  l.wf_type ='NEW' then 'NEW' when l.wf_type ='Z' then 'RUN' else 'CLO' end) as 'wfType',");
//		sql.append(" 'NEW' as 'wfType',");
//		sql.append(" round(l.loana_mount ,4)/10000 as 'loana_mount', ");
//		sql.append(" date_format(l.create_date, '%Y-%m-%d %H:%i:%s') as 'cdate',opr.province_name");//,NULL as 'sdate',NULL as 'enddate'
//		sql.append("  from o2o_loanapply l");
//		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
//		sql.append("  LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ,o2o_loan_user u ");
//		sql.append(" where   l.wf_type ='T' and  l.bill_code is not NULL ");
//		
//		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
//		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
//			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
//		}else {
//			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
//		}
//		
//		sql.append(" and l.loanapply_no=u.loanapply_no and u.spouse_id is null and u.sponsor_type is null");
//		sql.append("  and  l.loanapply_no not in(SELECT s.loanapply_no from  wf_type_status s )");
//		sql.append(" ) t where 1=1 and wf_type not in ('YQ','DC','HN','O') ");
//		if (StringUtil.hasText(oldO2oLikeForm.getBillCode())) {
//			sql.append(" and bill_code like ?");
//			pageQuery.addLikeParam(oldO2oLikeForm.getBillCode());
//		}
//		if (StringUtil.hasText(oldO2oLikeForm.getLoanType())) {
//			sql.append(" and loanType in (");
//			String loanTypeA [] = {};  
//			String loanType = "";
//			StringBuffer loanTypes = new StringBuffer();
//			if(oldO2oLikeForm.getLoanType().length()>4){
//				loanTypeA = oldO2oLikeForm.getLoanType().split(",");
//				for (int i = 0; i < loanTypeA.length; i++) {
//					loanTypes.append("'" + loanTypeA[i] + "',");
//				}
//			}else{
//				loanTypes.append("'"+oldO2oLikeForm.getLoanType()+"',");
//			}
//			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
//			sql.append(loanType);
//			sql.append(")");
//		}
//		if (StringUtil.hasText(oldO2oLikeForm.getPayMentStatus())) {
//			if ("O".equals(oldO2oLikeForm.getPayMentStatus())) {
//				sql.append(" and wf_type in ('YQ','DC','HN','O')");
//			} else if("ZXSX".equals(oldO2oLikeForm.getPayMentStatus())){
//				sql.append(" and wf_type=0");
//			}else {
//				sql.append(" and wf_type in (");
//				String wfTypeA[] = {};
//				String wfType = "";
//				StringBuffer wfTypes = new StringBuffer();
//				if (oldO2oLikeForm.getPayMentStatus().contains(",")) {
//					wfTypeA = oldO2oLikeForm.getPayMentStatus().split(",");
//					for (int i = 0; i < wfTypeA.length; i++) {
//						wfTypes.append("'" + wfTypeA[i] + "',");
//					}
//				} else {
//					wfTypes.append("'" + oldO2oLikeForm.getPayMentStatus() + "',");
//				}
//				wfType = wfTypes.toString().substring(0, wfTypes.toString().length() - 1);
//				sql.append(wfType);
//				sql.append(" ) ");
//			}
//		}
//		if (StringUtil.hasText(oldO2oLikeForm.getOperatorName())) {
//			sql.append(" and operator like ?");
//			pageQuery.addLikeParam(oldO2oLikeForm.getOperatorName());
//		}
//		if (StringUtil.hasText(oldO2oLikeForm.getUserSurname())) {
//			sql.append(" and user_full_name like ?");
//			pageQuery.addLikeParam(oldO2oLikeForm.getUserSurname());
//		}
//		if (StringUtil.hasText(oldO2oLikeForm.getLoadName())) {
//			sql.append(" and ltdName like ?");
//			pageQuery.addLikeParam(oldO2oLikeForm.getLoadName());
//		}
//		if (StringUtil.hasText(oldO2oLikeForm.getStartTime())) {
//			sql.append(" and cdate >= ? ");
//			pageQuery.addParam(oldO2oLikeForm.getStartTime());
//		}
//		if (StringUtil.hasText(oldO2oLikeForm.getEndTime())) {
//			sql.append(" and cdate <= ? ");
//			GregorianCalendar gc = new GregorianCalendar();
//			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEndTime()));
//			gc.add(5, 1);
//			pageQuery.addParam(gc.getTime());
//		}
//		
//		if (StringUtil.hasText(oldO2oLikeForm.getStTime())) {
//			sql.append(" and sdate >= ? ");
//			pageQuery.addParam(oldO2oLikeForm.getStTime());
//		}
//		if (StringUtil.hasText(oldO2oLikeForm.getSendTime())) {
//			sql.append(" and sdate <= ? ");
//			GregorianCalendar gc = new GregorianCalendar();
//			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getSendTime()));
//			gc.add(5, 1);
//			pageQuery.addParam(gc.getTime());
//		}
//		if (StringUtil.hasText(oldO2oLikeForm.getEendTime())) {
//			sql.append(" and enddate >= ? ");
//			pageQuery.addParam(oldO2oLikeForm.getEendTime());
//		}
//		if (StringUtil.hasText(oldO2oLikeForm.getEendendTime())) {
//			sql.append(" and enddate <= ? ");
//			GregorianCalendar gc = new GregorianCalendar();
//			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEendendTime()));
//			gc.add(5, 1);
//			pageQuery.addParam(gc.getTime());
//		}
//		
//		if (!StringUtil.isEmpty(oldO2oLikeForm.getHostProvince())) {
//			sql.append(" AND  province_code  = ? ");
//			pageQuery.addParam(oldO2oLikeForm.getHostProvince());
//		}
//		if (!StringUtil.isEmpty(oldO2oLikeForm.getBillStatus())) {
//			sql.append(" AND  wfType  = ? ");
//			pageQuery.addParam(oldO2oLikeForm.getBillStatus());
//		}
//		sql.append(" ORDER BY cdate desc ");
//		pageQuery.setQuerySql(sql.toString());
//		return this.findDataWithPage(pageQuery);
//	}
	
	
	/**
	 * 内审贷款分页查询
	 * @param OldO2oLikeForm
	 * @return
	 */
	public PageResult<Object[]> findNSByPage(OldO2oLikeForm oldO2oLikeForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class,oldO2oLikeForm.getPageSize() ,
				oldO2oLikeForm.getPageIndex() == null ? BaseUtils.PAGE_INDEX : oldO2oLikeForm.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * from ( ");
		sql.append(" select ");
		sql.append(" l.loanapply_no, l.bill_code,u.user_full_name,");
		sql.append(" CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount	) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END)");
		sql.append(" IS NULL THEN  l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2))/10000 as 'loana_mount', ");
		sql.append(" l.ea_mount/10000 ea_mount ,l.loan_type as 'loanType',l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区,l.wf_type,");
//		sql.append(" (case when  l.wf_type ='ZF' then 'NEW' when l.wf_type ='N' then 'RUN' else 'CLO' end) as wfType,");
		sql.append(" (case when  s.wf_status is NULL then 'NEW'  else s.wf_status end) as 'wfType',");
		sql.append(" e.bill_status,");
		sql.append(" (SELECT st.end_date from wf_type_status st where st.wf_type='ZX' and st.loanapply_no=l.loanapply_no limit 1) as 'cdate',");
		sql.append(" s.start_date as 'sdate',s.end_date as 'enddate',opr.province_code");
		sql.append("  from o2o_loanapply l  ");
		sql.append(" LEFT JOIN o2o_loan_user u on l.loan_id=u.loan_id and u.spouse_id is null and u.sponsor_type is null  ");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append("  LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ");
		sql.append(" LEFT JOIN wf_type_status s on s.loanapply_no=l.loanapply_no and s.wf_type in ('NS')");
		sql.append(" where  ");		
		sql.append(" l.islock='N' and l.loanapply_no=u.loanapply_no ");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		sql.append(" and l.wf_type not in('NEW','Z','T') and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL ");
		sql.append(" union");
		sql.append(" SELECT l.loanapply_no, l.bill_code, u.user_full_name, ");
		sql.append(" CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount	) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END)");
		sql.append(" IS NULL THEN  l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2))/10000 as 'loana_mount', ");
		sql.append(" l.ea_mount/10000 ea_mount,l.loan_type as 'loanType',l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区,");
		sql.append(" l.wf_type,s.wf_status as wfType,e.bill_status,");
		sql.append(" (SELECT st.end_date from wf_type_status st where st.wf_type='ZX' and st.loanapply_no=l.loanapply_no limit 1) as 'cdate',");
		sql.append(" s.start_date as 'sdate',NULL as 'enddate',opr.province_code");
		sql.append("  from o2o_loanapply l");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append("  LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ,");
		sql.append(" (SELECT * from (");
		sql.append(" SELECT s.* from wf_type_status s where ");
		sql.append(" s.wf_type in ('ZX','NS','BRT') group by loanapply_no,wf_type order by s.start_date	desc");
		sql.append(" ) t group by t.loanapply_no having count(*) =2 ");
		sql.append(" ) s ,o2o_loan_user u ");
		sql.append(" where  ");
		sql.append("  l.wf_type ='T' and s.loanapply_no=l.loanapply_no and s.wf_status in ('NEW','RUN') ");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		sql.append(" and l.loanapply_no=u.loanapply_no and u.spouse_id is null and u.sponsor_type is null");
		sql.append("  and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL ");
		sql.append(" union");
		sql.append(" SELECT l.loanapply_no, l.bill_code, u.user_full_name, ");
		sql.append(" CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount	) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END)");
		sql.append("  IS NULL THEN  l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2))/10000 as 'loana_mount', ");
		sql.append("  l.ea_mount/10000 ea_mount,l.loan_type as 'loanType',l.operator_name as 'operator', ");
		sql.append("  CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区,");
		sql.append(" l.wf_type,'NEW' as wfType,e.bill_status,s.end_date as 'cdate',NULL as 'sdate',NULL as 'enddate',opr.province_code");
		sql.append(" from o2o_loanapply l");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append("  LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ,");
		sql.append(" (SELECT * from (");
		sql.append(" SELECT s.* from wf_type_status s where ");
		sql.append(" s.wf_type in ('ZX','NS','BRT') group by loanapply_no,wf_type order by s.start_date	desc");
		sql.append(" ) t group by t.loanapply_no having count(*) =1 ");
		sql.append(" ) s ,o2o_loan_user u ");
		sql.append(" where ");
		sql.append("  l.wf_type ='T' and s.loanapply_no=l.loanapply_no and s.wf_status='CLO'");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		sql.append(" and l.loanapply_no=u.loanapply_no and u.spouse_id is null and u.sponsor_type is null");
		sql.append(" and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL ");
		sql.append(" ) t where 1=1 and wf_type not in ('YQ','DC','HN','O') ");
		if (StringUtil.hasText(oldO2oLikeForm.getBillCode())) {
			sql.append(" and bill_code like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getBillCode());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getLoanType())) {
			sql.append(" and loanType in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(oldO2oLikeForm.getLoanType().length()>4){
				loanTypeA = oldO2oLikeForm.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+oldO2oLikeForm.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			sql.append(loanType);
			sql.append(")");
		}
		if (StringUtil.hasText(oldO2oLikeForm.getPayMentStatus())) {
			if ("O".equals(oldO2oLikeForm.getPayMentStatus())) {
				sql.append(" and wf_type in ('YQ','DC','HN','O')");
			} else if("ZXSX".equals(oldO2oLikeForm.getPayMentStatus())){
				sql.append(" and wf_type=0");
			}else {
				sql.append(" and wf_type in (");
				String wfTypeA[] = {};
				String wfType = "";
				StringBuffer wfTypes = new StringBuffer();
				if (oldO2oLikeForm.getPayMentStatus().contains(",")) {
					wfTypeA = oldO2oLikeForm.getPayMentStatus().split(",");
					for (int i = 0; i < wfTypeA.length; i++) {
						wfTypes.append("'" + wfTypeA[i] + "',");
					}
				} else {
					wfTypes.append("'" + oldO2oLikeForm.getPayMentStatus() + "',");
				}
				wfType = wfTypes.toString().substring(0, wfTypes.toString().length() - 1);
				sql.append(wfType);
				sql.append(" ) ");
			}
		}
		if (StringUtil.hasText(oldO2oLikeForm.getOperatorName())) {
			sql.append(" and operator like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getOperatorName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getUserSurname())) {
			sql.append(" and user_full_name like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getUserSurname());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getLoadName())) {
			sql.append(" and ltdName like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getLoadName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getStartTime())) {
			sql.append(" and cdate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getStartTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEndTime())) {
			sql.append(" and cdate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEndTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		if (StringUtil.hasText(oldO2oLikeForm.getStTime())) {
			sql.append(" and sdate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getStTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getSendTime())) {
			sql.append(" and sdate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getSendTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEendTime())) {
			sql.append(" and enddate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getEendTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEendendTime())) {
			sql.append(" and enddate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEendendTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		if (!StringUtil.isEmpty(oldO2oLikeForm.getHostProvince())) {
			sql.append(" AND  province_code  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getHostProvince());
		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getBillStatus())) {
			sql.append(" AND  wfType  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getBillStatus());
		}
		sql.append(" ORDER BY cdate desc ");
		// pageQuery.setSumColumnIndexs(new int[]{10});
		pageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(pageQuery);
	}
	
	public PageResult<Object[]> findNSByPageExport(OldO2oLikeForm oldO2oLikeForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class,oldO2oLikeForm.getPageSize() ,
				oldO2oLikeForm.getPageIndex() == null ? BaseUtils.PAGE_INDEX : oldO2oLikeForm.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT bill_code,user_full_name,loana_mount/10000 loana_mount,ea_mount/10000 ea_mount,operator,ltdName,省区,(select d.item_name from PUB_DDITEM d where d.DD_NO=11321 and d.item_code= loanType ) as 贷款类型 ,(case when  wfType ='NEW' then '新建' when wfType ='RUN' then '审核中' else '结束' end) as wfType,DATE_FORMAT(cdate,'%Y-%m-%d') as cdate from ( ");
		sql.append(" select ");
		sql.append(" l.loanapply_no, l.bill_code,u.user_full_name,");
		sql.append(" CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount	) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END)");
		sql.append(" IS NULL THEN  l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2)) as 'loana_mount', ");
		sql.append(" l.ea_mount ,l.loan_type as 'loanType',l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区,l.wf_type,");
//		sql.append(" (case when  l.wf_type ='ZF' then 'NEW' when l.wf_type ='N' then 'RUN' else 'CLO' end) as wfType,");
		sql.append(" (case when  s.wf_status is NULL then 'NEW'  else s.wf_status end) as 'wfType',");
		sql.append(" e.bill_status,");
		sql.append(" (SELECT st.end_date from wf_type_status st where st.wf_type='ZX' and st.loanapply_no=l.loanapply_no limit 1) as 'cdate',");
		sql.append(" s.start_date as 'sdate',s.end_date as 'enddate',opr.province_code");
		sql.append("  from o2o_loanapply l  ");
		sql.append(" LEFT JOIN o2o_loan_user u on l.loan_id=u.loan_id and u.spouse_id is null and u.sponsor_type is null  ");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append("  LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ");
		sql.append(" LEFT JOIN wf_type_status s on s.loanapply_no=l.loanapply_no and s.wf_type in ('NS')");
		sql.append(" where  ");
		sql.append("  l.islock='N' and l.loanapply_no=u.loanapply_no ");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		sql.append(" and l.wf_type not in('NEW','Z','T') and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL ");
		sql.append(" union");
		sql.append(" SELECT l.loanapply_no, l.bill_code, u.user_full_name, ");
		sql.append(" CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount	) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END)");
		sql.append(" IS NULL THEN  l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2)) as 'loana_mount', ");
		sql.append(" l.ea_mount ,l.loan_type as 'loanType',l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区,");
		sql.append(" l.wf_type,s.wf_status as wfType,e.bill_status,");
		sql.append(" (SELECT st.end_date from wf_type_status st where st.wf_type='ZX' and st.loanapply_no=l.loanapply_no limit 1) as 'cdate',");
		sql.append(" s.start_date as 'sdate',NULL as 'enddate',opr.province_code");
		sql.append("  from o2o_loanapply l");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append("  LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ,");
		sql.append(" (SELECT * from (");
		sql.append(" SELECT s.* from wf_type_status s where ");
		sql.append(" s.wf_type in ('ZX','NS','BRT') group by loanapply_no,wf_type order by s.start_date	desc");
		sql.append(" ) t group by t.loanapply_no having count(*) =2 ");
		sql.append(" ) s ,o2o_loan_user u ");
		sql.append(" where  ");
		sql.append("  l.wf_type ='T' and s.loanapply_no=l.loanapply_no and s.wf_status in ('NEW','RUN') ");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		sql.append(" and l.loanapply_no=u.loanapply_no and u.spouse_id is null and u.sponsor_type is null");
		sql.append("  and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL ");
		sql.append(" union");
		sql.append(" SELECT l.loanapply_no, l.bill_code, u.user_full_name, ");
		sql.append(" CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount	) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END)");
		sql.append("  IS NULL THEN  l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2)) as 'loana_mount', ");
		sql.append("  l.ea_mount ,l.loan_type as 'loanType',l.operator_name as 'operator', ");
		sql.append("  CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区,");
		sql.append(" l.wf_type,'NEW' as wfType,e.bill_status,s.end_date as 'cdate',NULL as 'sdate',NULL as 'enddate',opr.province_code");
		sql.append(" from o2o_loanapply l");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append("  LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ,");
		sql.append(" (SELECT * from (");
		sql.append(" SELECT s.* from wf_type_status s where ");
		sql.append(" s.wf_type in ('ZX','NS','BRT') group by loanapply_no,wf_type order by s.start_date	desc");
		sql.append(" ) t group by t.loanapply_no having count(*) =1 ");
		sql.append(" ) s ,o2o_loan_user u ");
		sql.append(" where ");
		sql.append(" l.wf_type ='T' and s.loanapply_no=l.loanapply_no and s.wf_status='CLO'");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		sql.append(" and l.loanapply_no=u.loanapply_no and u.spouse_id is null and u.sponsor_type is null");
		sql.append(" and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL ");
		sql.append(" ) t where 1=1 and wf_type not in ('YQ','DC','HN','O') ");
		if (StringUtil.hasText(oldO2oLikeForm.getBillCode())) {
			sql.append(" and bill_code like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getBillCode());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getLoanType())) {
			sql.append(" and loanType in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(oldO2oLikeForm.getLoanType().length()>4){
				loanTypeA = oldO2oLikeForm.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+oldO2oLikeForm.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			sql.append(loanType);
			sql.append(")");
		}
		if (StringUtil.hasText(oldO2oLikeForm.getPayMentStatus())) {
			if ("O".equals(oldO2oLikeForm.getPayMentStatus())) {
				sql.append(" and wf_type in ('YQ','DC','HN','O')");
			} else if("ZXSX".equals(oldO2oLikeForm.getPayMentStatus())){
				sql.append(" and wf_type=0");
			}else {
				sql.append(" and wf_type in (");
				String wfTypeA[] = {};
				String wfType = "";
				StringBuffer wfTypes = new StringBuffer();
				if (oldO2oLikeForm.getPayMentStatus().contains(",")) {
					wfTypeA = oldO2oLikeForm.getPayMentStatus().split(",");
					for (int i = 0; i < wfTypeA.length; i++) {
						wfTypes.append("'" + wfTypeA[i] + "',");
					}
				} else {
					wfTypes.append("'" + oldO2oLikeForm.getPayMentStatus() + "',");
				}
				wfType = wfTypes.toString().substring(0, wfTypes.toString().length() - 1);
				sql.append(wfType);
				sql.append(" ) ");
			}
		}
		if (StringUtil.hasText(oldO2oLikeForm.getOperatorName())) {
			sql.append(" and operator like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getOperatorName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getUserSurname())) {
			sql.append(" and user_full_name like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getUserSurname());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getLoadName())) {
			sql.append(" and ltdName like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getLoadName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getStartTime())) {
			sql.append(" and cdate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getStartTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEndTime())) {
			sql.append(" and cdate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEndTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		if (StringUtil.hasText(oldO2oLikeForm.getStTime())) {
			sql.append(" and sdate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getStTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getSendTime())) {
			sql.append(" and sdate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getSendTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEendTime())) {
			sql.append(" and enddate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getEendTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEendendTime())) {
			sql.append(" and enddate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEendendTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		if (!StringUtil.isEmpty(oldO2oLikeForm.getHostProvince())) {
			sql.append(" AND  province_code  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getHostProvince());
		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getBillStatus())) {
			sql.append(" AND  wfType  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getBillStatus());
		}
		sql.append(" ORDER BY cdate desc ");
		pageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(pageQuery);
	}
	
	
	/**
	 * 财务审核分页查询
	 * @param OldO2oLikeForm
	 * @return
	 */
	public PageResult<Object[]> findCWSHByPage(OldO2oLikeForm oldO2oLikeForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class,oldO2oLikeForm.getPageSize() ,
				oldO2oLikeForm.getPageIndex() == null ? BaseUtils.PAGE_INDEX : oldO2oLikeForm.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append(" select l.loanapply_no,(case when l.bill_code is null then '查看' else l.bill_code end) as 'billCode', ");
		sql.append(" u.user_full_name as 'userName', l.loan_type, l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区,");
		sql.append(" CONCAT(l.customer_code,'-',l.custome_rname) as 'customername' ,l.wf_type,");
		//sql.append(" l.custome_rname as 'customername' ,");
		sql.append("(case when  os.flow_status ='NEW' then '新建' when os.flow_status ='RUN' then '流程中' else '流程结束' end) as 'wfType', ");
		sql.append(" os.startup_time AS 'startup_time',os.end_time from O2O_FINANCE_FLOW_STATUS os LEFT JOIN o2o_loanapply l on l.loanapply_no = os.loanapplyno LEFT JOIN o2o_loan_user u on l.loan_id=u.loan_id and u.spouse_id is null and u.sponsor_type is null ");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append(" LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no where ");
		
		sql.append(" 1=1 and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getXdjlNo())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getXdjlNo()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.ltd)**/ 1=1 ) ");
		}
		sql.append(" and l.islock='N' and l.loanapply_no=u.loanapply_no ");
		
		//sql.append(" /**DATA_PERMISSION(l.LTD)**/ 1=1 and l.islock='N' and l.loanapply_no=u.loanapply_no ");
		
		if (StringUtil.hasText(oldO2oLikeForm.getBillCode())) {
			sql.append(" and l.bill_code like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getBillCode());
		}
		
		if (StringUtil.hasText(oldO2oLikeForm.getCustomername())) {
			sql.append(" and CONCAT(l.customer_code,'-',l.custome_rname) like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getCustomername());
		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getBillStatus())) {
			sql.append(" AND  os.flow_status  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getBillStatus());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEendTime())) {
			sql.append(" and os.end_time >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getEendTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEendendTime())) {
			sql.append(" and os.end_time <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEendendTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getLoanType())) {
			sql.append(" and l.loan_type in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(oldO2oLikeForm.getLoanType().length()>4){
				loanTypeA = oldO2oLikeForm.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+oldO2oLikeForm.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			sql.append(loanType);
			sql.append(")");
		}
		if (StringUtil.hasText(oldO2oLikeForm.getPayMentStatus())) {
			if ("O".equals(oldO2oLikeForm.getPayMentStatus())) {
				sql.append(" and l.wf_type in ('YQ','DC','HN','O')");
			} else if("ZXSX".equals(oldO2oLikeForm.getPayMentStatus())){
				sql.append(" and l.credit_status=0");
			}else {
				sql.append(" and l.wf_type=?");
				pageQuery.addParam(oldO2oLikeForm.getPayMentStatus());
			}
		}
		if (StringUtil.hasText(oldO2oLikeForm.getOperatorName())) {
			sql.append(" and l.operator_name like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getOperatorName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getUserSurname())) {
			sql.append(" and u.user_full_name like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getUserSurname());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getBankType())) {
			sql.append(" and l.bank_type = ?");
			pageQuery.addParam(oldO2oLikeForm.getBankType());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getLoadName())) {
			sql.append(" and CONCAT(l.ltd_code,'-',l.ltd_name) like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getLoadName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getBuybackName())) {
			sql.append(" and l.buyback_name like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getBuybackName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getStartTime())) {
			sql.append(" and l.create_date >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getStartTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEndTime())) {
			sql.append(" and l.create_date <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEndTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getOnPlace())) {
			sql.append(" and l.is_place_on_file = ? ");
			pageQuery.addParam(oldO2oLikeForm.getOnPlace());
		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getHostProvince())) {
			sql.append(" AND  opr.province_code  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getHostProvince());
		}
		if(!StringUtil.isEmpty(oldO2oLikeForm.getPartnerId())){
			sql.append(" AND l.partner_id  in (");
			sql.append(oldO2oLikeForm.getPartnerId());
			sql.append(")");
		}
		sql.append(" order by l.create_date	desc ");
		// pageQuery.setSumColumnIndexs(new int[]{10});
		pageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(pageQuery);
	}
	
	/**
	 * 财务审核导出
	 * @param OldO2oLikeForm
	 * @return
	 */
	public PageResult<Object[]> findCWSHByPageExport(OldO2oLikeForm oldO2oLikeForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class,oldO2oLikeForm.getPageSize() ,
				oldO2oLikeForm.getPageIndex() == null ? BaseUtils.PAGE_INDEX : oldO2oLikeForm.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append(" select (case when l.bill_code is null then '查看' else l.bill_code end) as 'billCode', ");
		sql.append(" u.user_full_name as 'userName',l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区, CONCAT(l.customer_code,'-',l.custome_rname) as 'customername' ,");
		sql.append("(case when  os.flow_status ='NEW' then '新建' when os.flow_status ='RUN' then '流程中' else '流程结束' end) as 'wfType', ");
		sql.append(" DATE_FORMAT(os.startup_time,'%Y-%m-%d') AS 'startup_time' from O2O_FINANCE_FLOW_STATUS os LEFT JOIN o2o_loanapply l on l.loanapply_no = os.loanapplyno LEFT JOIN o2o_loan_user u on l.loan_id=u.loan_id and u.spouse_id is null and u.sponsor_type is null ");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append(" LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no where ");
		
		sql.append(" 1=1 and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getXdjlNo())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getXdjlNo()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.ltd)**/ 1=1 ) ");
		}
		sql.append(" and l.islock='N' and l.loanapply_no=u.loanapply_no ");
		//sql.append(" /**DATA_PERMISSION(l.LTD)**/ 1=1 and l.islock='N' and l.loanapply_no=u.loanapply_no ");
		
		if (StringUtil.hasText(oldO2oLikeForm.getBillCode())) {
			sql.append(" and l.bill_code like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getBillCode());
		}
		
		if (StringUtil.hasText(oldO2oLikeForm.getCustomername())) {
			sql.append(" and CONCAT(l.customer_code,'-',l.custome_rname) like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getCustomername());
		}
		
		if (StringUtil.hasText(oldO2oLikeForm.getLoanType())) {
			sql.append(" and l.loan_type in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(oldO2oLikeForm.getLoanType().length()>4){
				loanTypeA = oldO2oLikeForm.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+oldO2oLikeForm.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			sql.append(loanType);
			sql.append(")");
		}
		if (StringUtil.hasText(oldO2oLikeForm.getPayMentStatus())) {
			if ("O".equals(oldO2oLikeForm.getPayMentStatus())) {
				sql.append(" and l.wf_type in ('YQ','DC','HN','O')");
			} else if("ZXSX".equals(oldO2oLikeForm.getPayMentStatus())){
				sql.append(" and l.credit_status=0");
			}else {
				sql.append(" and l.wf_type=?");
				pageQuery.addParam(oldO2oLikeForm.getPayMentStatus());
			}
		}
		if (StringUtil.hasText(oldO2oLikeForm.getOperatorName())) {
			sql.append(" and l.operator_name like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getOperatorName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getUserSurname())) {
			sql.append(" and u.user_full_name like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getUserSurname());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getBankType())) {
			sql.append(" and l.bank_type = ?");
			pageQuery.addParam(oldO2oLikeForm.getBankType());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getLoadName())) {
			sql.append(" and CONCAT(l.ltd_code,'-',l.ltd_name) like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getLoadName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getBuybackName())) {
			sql.append(" and l.buyback_name like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getBuybackName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getStartTime())) {
			sql.append(" and l.create_date >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getStartTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEndTime())) {
			sql.append(" and l.create_date <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEndTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getOnPlace())) {
			sql.append(" and l.is_place_on_file = ? ");
			pageQuery.addParam(oldO2oLikeForm.getOnPlace());
		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getHostProvince())) {
			sql.append(" AND  opr.province_code  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getHostProvince());
		}
		if(!StringUtil.isEmpty(oldO2oLikeForm.getPartnerId())){
			sql.append(" AND l.partner_id  in (");
			sql.append(oldO2oLikeForm.getPartnerId());
			sql.append(")");
		}
		sql.append(" order by l.create_date	desc ");
		// pageQuery.setSumColumnIndexs(new int[]{10});
		pageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(pageQuery);
	}
	
	
	/**
	 * 面签申报分页查询
	 * @param OldO2oLikeForm
	 * @return
	 */
	public PageResult<Object[]> findMQSBByPage(OldO2oLikeForm oldO2oLikeForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class,oldO2oLikeForm.getPageSize() ,
				oldO2oLikeForm.getPageIndex() == null ? BaseUtils.PAGE_INDEX : oldO2oLikeForm.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * from (");
		sql.append(" select l.loanapply_no, l.bill_code,u.user_full_name, ");
		sql.append(" l.bank_loan_name, CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount	) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END)");
		sql.append(" IS NULL THEN  l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2))/10000 as 'loana_mount', ");
		sql.append(" l.ea_mount/10000 ea_mount ,l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区,");
		sql.append(" l.loan_type as 'loanType',l.wf_type,");
//		sql.append(" (case when  l.wf_type ='NF' then 'NEW' when l.wf_type ='S' then 'RUN' else 'CLO' end) as 'wfType',");
		sql.append(" (case when  s.wf_status is NULL then 'NEW'  else s.wf_status end) as 'wfType',");
		sql.append(" e.bill_status, ");
		sql.append(" (SELECT st.end_date from wf_type_status st where st.wf_type='NS' and st.loanapply_no=l.loanapply_no limit 1) as 'cdate',");
		sql.append(" s.start_date as 'sdate',s.end_date as 'enddate',opr.province_code");
		sql.append(" from o2o_loanapply l ");
		sql.append(" LEFT JOIN o2o_loan_user u on l.loan_id=u.loan_id and u.spouse_id is null and u.sponsor_type is null  ");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd  ");
		sql.append(" LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ");
		sql.append(" LEFT JOIN wf_type_status s on s.loanapply_no=l.loanapply_no and s.wf_type in ('BRT')");
		sql.append(" where ");
		sql.append("  l.islock='N' and l.loanapply_no=u.loanapply_no ");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		sql.append(" and l.wf_type not in('NEW','Z','ZF','T','N')  and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL");
		sql.append(" union");
		sql.append(" SELECT l.loanapply_no, l.bill_code,u.user_full_name, ");
		sql.append(" l.bank_loan_name, CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END)");
		sql.append(" IS NULL THEN  l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2))/10000 as 'loana_mount', ");
		sql.append(" l.ea_mount/10000 ea_mount  ,l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区,");
		sql.append(" l.loan_type as 'loanType',l.wf_type,");
//		sql.append(" (case when  l.wf_type ='NF' then 'NEW' when l.wf_type ='S' then 'RUN' else 'CLO' end) as 'wfType',");
		sql.append(" s.wf_status as wfType,");
		sql.append(" e.bill_status, ");
		sql.append(" (SELECT st.end_date from wf_type_status st where st.wf_type='NS' and st.loanapply_no=l.loanapply_no limit 1) as 'cdate',");
		sql.append(" s.start_date as 'sdate',NULL as 'enddate',opr.province_code");
		sql.append(" from o2o_loanapply l");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append(" LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ,");
		sql.append(" (SELECT * from (");
		sql.append(" SELECT s.* from wf_type_status s where ");
		sql.append(" s.wf_type in ('ZX','NS','BRT') group by loanapply_no,wf_type order by s.start_date	desc");
		sql.append(" ) t group by t.loanapply_no having count(*) =3 ");
		sql.append(" )s ,o2o_loan_user u ");
		sql.append(" where  l.wf_type ='T' and s.loanapply_no=l.loanapply_no and s.wf_status in ('NEW','RUN') ");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		sql.append(" and l.loanapply_no=u.loanapply_no and u.spouse_id is null and u.sponsor_type is null");
		sql.append(" and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL");
		sql.append(" union");
		sql.append(" SELECT l.loanapply_no, l.bill_code,u.user_full_name, ");
		sql.append(" l.bank_loan_name, CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount	) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END)");
		sql.append(" IS NULL THEN  l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2))/10000 as 'loana_mount', ");
		sql.append(" l.ea_mount/10000 ea_mount ,l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区,");
		sql.append(" l.loan_type as 'loanType',l.wf_type,");
//		sql.append(" (case when  l.wf_type ='NF' then 'NEW' when l.wf_type ='S' then 'RUN' else 'CLO' end) as 'wfType',");
		sql.append(" 'NEW' as 'wfType',");
		sql.append(" e.bill_status,  ");
		sql.append(" s.end_date as 'cdate',NULL as 'sdate',NULL as 'enddate',opr.province_code");
		sql.append(" from o2o_loanapply l");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append(" LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ,");
		sql.append(" (SELECT * from (");
		sql.append(" SELECT s.* from wf_type_status s where ");
		sql.append("  s.wf_type in ('ZX','NS','BRT') group by loanapply_no,wf_type order by s.start_date	desc");
		sql.append(" ) t group by t.loanapply_no having count(*) =2 ");
		sql.append(" ) s ,o2o_loan_user u ");
		sql.append(" where  l.wf_type ='T' and s.loanapply_no=l.loanapply_no and s.wf_status='CLO'");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		sql.append(" and l.loanapply_no=u.loanapply_no and u.spouse_id is null and u.sponsor_type is null");
		sql.append(" and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL ");
		sql.append(" ) t where 1=1  and wf_type not in ('YQ','DC','HN','O') ");
		if (StringUtil.hasText(oldO2oLikeForm.getBillCode())) {
			sql.append(" and bill_code like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getBillCode());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getLoanType())) {
			sql.append(" and loanType in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(oldO2oLikeForm.getLoanType().length()>4){
				loanTypeA = oldO2oLikeForm.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+oldO2oLikeForm.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			sql.append(loanType);
			sql.append(")");
		}
		if (StringUtil.hasText(oldO2oLikeForm.getPayMentStatus())) {
			if ("O".equals(oldO2oLikeForm.getPayMentStatus())) {
				sql.append(" and wf_type in ('YQ','DC','HN','O')");
			} else if("ZXSX".equals(oldO2oLikeForm.getPayMentStatus())){
				sql.append(" and wf_type=0");
			}else {
				sql.append(" and wf_type in (");
				String wfTypeA[] = {};
				String wfType = "";
				StringBuffer wfTypes = new StringBuffer();
				if (oldO2oLikeForm.getPayMentStatus().contains(",")) {
					wfTypeA = oldO2oLikeForm.getPayMentStatus().split(",");
					for (int i = 0; i < wfTypeA.length; i++) {
						wfTypes.append("'" + wfTypeA[i] + "',");
					}
				} else {
					wfTypes.append("'" + oldO2oLikeForm.getPayMentStatus() + "',");
				}
				wfType = wfTypes.toString().substring(0, wfTypes.toString().length() - 1);
				sql.append(wfType);
				sql.append(" ) ");
			}
		}
		if (StringUtil.hasText(oldO2oLikeForm.getOperatorName())) {
			sql.append(" and operator like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getOperatorName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getUserSurname())) {
			sql.append(" and user_full_name like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getUserSurname());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getLoadName())) {
			sql.append(" and ltdName like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getLoadName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getStartTime())) {
			sql.append(" and cdate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getStartTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEndTime())) {
			sql.append(" and cdate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEndTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		if (StringUtil.hasText(oldO2oLikeForm.getStTime())) {
			sql.append(" and sdate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getStTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getSendTime())) {
			sql.append(" and sdate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getSendTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEendTime())) {
			sql.append(" and enddate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getEendTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEendendTime())) {
			sql.append(" and enddate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEendendTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		if (!StringUtil.isEmpty(oldO2oLikeForm.getHostProvince())) {
			sql.append(" AND  province_code  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getHostProvince());
		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getBankType())) {
			sql.append(" AND  bank_loan_name  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getBankType());
		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getBillStatus())) {
			sql.append(" AND  wfType  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getBillStatus());
		}
		sql.append(" ORDER BY cdate desc ");
		// pageQuery.setSumColumnIndexs(new int[]{10});
		pageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(pageQuery);
	}
	
	public PageResult<Object[]> findMQSBByPageExport(OldO2oLikeForm oldO2oLikeForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class,oldO2oLikeForm.getPageSize() ,
				oldO2oLikeForm.getPageIndex() == null ? BaseUtils.PAGE_INDEX : oldO2oLikeForm.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT bill_code,user_full_name,loana_mount,ea_mount,operator,ltdName,省区,(select d.item_name from PUB_DDITEM d where d.DD_NO=11321 and d.item_code= loanType ) as 贷款类型 ,(case when  wfType ='NEW' then '新建' when wfType ='RUN' then '审核中' else '结束' end) as wfType,DATE_FORMAT(cdate,'%Y-%m-%d') as cdate from (");
		sql.append(" select l.loanapply_no, l.bill_code,u.user_full_name, ");
		sql.append(" l.bank_loan_name, CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount	) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END)");
		sql.append(" IS NULL THEN  l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2))/10000 as 'loana_mount', ");
		sql.append(" l.ea_mount/10000 ea_mount  ,l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区,");
		sql.append(" l.loan_type as 'loanType',l.wf_type,");
//		sql.append(" (case when  l.wf_type ='NF' then 'NEW' when l.wf_type ='S' then 'RUN' else 'CLO' end) as 'wfType',");
		sql.append(" (case when  s.wf_status is NULL then 'NEW'  else s.wf_status end) as 'wfType',");
		sql.append(" e.bill_status,  ");
		sql.append(" (SELECT st.end_date from wf_type_status st where st.wf_type='NS' and st.loanapply_no=l.loanapply_no limit 1) as 'cdate',");
		sql.append(" s.start_date as 'sdate',s.end_date as 'enddate',opr.province_code");
		sql.append(" from o2o_loanapply l ");
		sql.append(" LEFT JOIN o2o_loan_user u on l.loan_id=u.loan_id and u.spouse_id is null and u.sponsor_type is null  ");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd  ");
		sql.append(" LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ");
		sql.append(" LEFT JOIN wf_type_status s on s.loanapply_no=l.loanapply_no and s.wf_type in ('BRT')");
		sql.append(" where ");
		sql.append("  l.islock='N' and l.loanapply_no=u.loanapply_no ");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		sql.append(" and l.wf_type not in('NEW','Z','ZF','T','N')  and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL");
		sql.append(" union");
		sql.append(" SELECT l.loanapply_no, l.bill_code,u.user_full_name, ");
		sql.append(" l.bank_loan_name, CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END)");
		sql.append(" IS NULL THEN  l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2))/10000 as 'loana_mount', ");
		sql.append(" l.ea_mount/10000  ea_mount,l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区,");
		sql.append(" l.loan_type as 'loanType',l.wf_type,");
//		sql.append(" (case when  l.wf_type ='NF' then 'NEW' when l.wf_type ='S' then 'RUN' else 'CLO' end) as 'wfType',");
		sql.append(" s.wf_status as wfType,");
		sql.append(" e.bill_status, ");
		sql.append(" (SELECT st.end_date from wf_type_status st where st.wf_type='NS' and st.loanapply_no=l.loanapply_no limit 1) as 'cdate',");
		sql.append(" s.start_date as 'sdate',NULL as 'enddate',opr.province_code");
		sql.append(" from o2o_loanapply l");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append(" LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ,");
		sql.append(" (SELECT * from (");
		sql.append(" SELECT s.* from wf_type_status s where ");
		sql.append(" s.wf_type in ('ZX','NS','BRT') group by loanapply_no,wf_type order by s.start_date	desc");
		sql.append(" ) t group by t.loanapply_no having count(*) =3 ");
		sql.append(" )s ,o2o_loan_user u ");
		sql.append(" where  l.wf_type ='T' and s.loanapply_no=l.loanapply_no and s.wf_status in ('NEW','RUN') ");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		sql.append(" and l.loanapply_no=u.loanapply_no and u.spouse_id is null and u.sponsor_type is null");
		sql.append(" and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL");
		sql.append(" union");
		sql.append(" SELECT l.loanapply_no, l.bill_code,u.user_full_name, ");
		sql.append(" l.bank_loan_name, CAST(ROUND((CASE WHEN(CASE WHEN (l.bank_amount	) IS NULL THEN l.ea_mount ELSE(l.bank_amount)END)");
		sql.append(" IS NULL THEN  l.loana_mount ELSE l.ea_mount END),2)as DECIMAL(10,2))/10000 as 'loana_mount', ");
		sql.append(" l.ea_mount/10000 ea_mount ,l.operator_name as 'operator', ");
		sql.append(" CONCAT(l.ltd_code,'-',l.ltd_name) as 'ltdName',opr.province_name AS 省区,");
		sql.append(" l.loan_type as 'loanType',l.wf_type,");
//		sql.append(" (case when  l.wf_type ='NF' then 'NEW' when l.wf_type ='S' then 'RUN' else 'CLO' end) as 'wfType',");
		sql.append(" 'NEW' as 'wfType',");
		sql.append(" e.bill_status,  ");
		sql.append(" s.end_date as 'cdate',NULL as 'sdate',NULL as 'enddate',opr.province_code");
		sql.append(" from o2o_loanapply l");
		sql.append(" LEFT JOIN o2o_platform_regional as opr ON opr.platform_id = l.ltd ");
		sql.append(" LEFT JOIN o2o_loanapply_expand e on e.loanapply_no=l.loanapply_no ,");
		sql.append(" (SELECT * from (");
		sql.append(" SELECT s.* from wf_type_status s where ");
		sql.append("  s.wf_type in ('ZX','NS','BRT') group by loanapply_no,wf_type order by s.start_date	desc");
		sql.append(" ) t group by t.loanapply_no having count(*) =2 ");
		sql.append(" ) s ,o2o_loan_user u ");
		sql.append(" where   l.wf_type ='T' and s.loanapply_no=l.loanapply_no and s.wf_status='CLO'");
		
		sql.append(" and (l.creat_ro="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(oldO2oLikeForm.getPartnerId())) {
			sql.append(" or l.operator_no in ("+oldO2oLikeForm.getPartnerId()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(l.LTD)**/  1=1 ) ");
		}
		
		sql.append(" and l.loanapply_no=u.loanapply_no and u.spouse_id is null and u.sponsor_type is null");
		sql.append(" and (l.data_source is null or l.data_source<>'2') and  l.bill_code is not NULL ");
		sql.append(" ) t where 1=1 and wf_type not in ('YQ','DC','HN','O') ");
		if (StringUtil.hasText(oldO2oLikeForm.getBillCode())) {
			sql.append(" and bill_code like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getBillCode());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getLoanType())) {
			sql.append(" and loanType in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(oldO2oLikeForm.getLoanType().length()>4){
				loanTypeA = oldO2oLikeForm.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+oldO2oLikeForm.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			sql.append(loanType);
			sql.append(")");
		}
		if (StringUtil.hasText(oldO2oLikeForm.getPayMentStatus())) {
			if ("O".equals(oldO2oLikeForm.getPayMentStatus())) {
				sql.append(" and wf_type in ('YQ','DC','HN','O')");
			} else if("ZXSX".equals(oldO2oLikeForm.getPayMentStatus())){
				sql.append(" and wf_type=0");
			}else {
				sql.append(" and wf_type in (");
				String wfTypeA[] = {};
				String wfType = "";
				StringBuffer wfTypes = new StringBuffer();
				if (oldO2oLikeForm.getPayMentStatus().contains(",")) {
					wfTypeA = oldO2oLikeForm.getPayMentStatus().split(",");
					for (int i = 0; i < wfTypeA.length; i++) {
						wfTypes.append("'" + wfTypeA[i] + "',");
					}
				} else {
					wfTypes.append("'" + oldO2oLikeForm.getPayMentStatus() + "',");
				}
				wfType = wfTypes.toString().substring(0, wfTypes.toString().length() - 1);
				sql.append(wfType);
				sql.append(" ) ");
			}
		}
		if (StringUtil.hasText(oldO2oLikeForm.getOperatorName())) {
			sql.append(" and operator like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getOperatorName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getUserSurname())) {
			sql.append(" and user_full_name like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getUserSurname());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getLoadName())) {
			sql.append(" and ltdName like ?");
			pageQuery.addLikeParam(oldO2oLikeForm.getLoadName());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getStartTime())) {
			sql.append(" and cdate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getStartTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEndTime())) {
			sql.append(" and cdate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEndTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		if (StringUtil.hasText(oldO2oLikeForm.getStTime())) {
			sql.append(" and sdate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getStTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getSendTime())) {
			sql.append(" and sdate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getSendTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEendTime())) {
			sql.append(" and enddate >= ? ");
			pageQuery.addParam(oldO2oLikeForm.getEendTime());
		}
		if (StringUtil.hasText(oldO2oLikeForm.getEendendTime())) {
			sql.append(" and enddate <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(oldO2oLikeForm.getEendendTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		if (!StringUtil.isEmpty(oldO2oLikeForm.getHostProvince())) {
			sql.append(" AND  province_code  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getHostProvince());
		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getBankType())) {
			sql.append(" AND  bank_loan_name  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getBankType());
		}
		if (!StringUtil.isEmpty(oldO2oLikeForm.getBillStatus())) {
			sql.append(" AND  wfType  = ? ");
			pageQuery.addParam(oldO2oLikeForm.getBillStatus());
		}
		sql.append(" ORDER BY cdate desc ");
		pageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(pageQuery);
	}
	
}
